--EXEC SP_WAFER_START_REPORT_NEW 0
--SELECT * FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( 2197, 0 ) WHERE PS_TYPE_NAME = 'ASSEMBLY'
ALTER PROC SP_WAFER_START_REPORT_NEW  @FAB_PROCESSSTEP_ID_INPUT INT
AS
BEGIN
	CREATE TABLE #FORECAST_TABLE
	(
		FG_PART_NUMBER VARCHAR(50), 
		FG_PS_ID INT,
		CQ_SHIPPED_QTY INT, --CURRENT QTR SHIPPED QTY
		CQ_BACKLOG_QTY INT, --CURRENT QTR SHIPPED QTY
		CQ_FORECAST_QTY INT, --CURRENT QTR FORECAST QTY
		Q1_FORECAST_QTY INT, --NEXT QTR FORECAST QTY
		Q2_FORECAST_QTY INT, --NEXT NEXT QTR FORECAST QTY
		Q3_FORECAST_QTY INT, --NEXT NEXT NEXT QTR FORECAST QTY
		M1_FORECAST_QTY INT, --CURRENT QTR START MONTH QTY
		M2_FORECAST_QTY INT, --NEXT MONTH FROM THE CURRENT QTR START MONTH QTY AND SO ON....
		M3_FORECAST_QTY INT,
		M4_FORECAST_QTY INT,
		M5_FORECAST_QTY INT,
		M6_FORECAST_QTY INT,
		M7_FORECAST_QTY INT,
		M8_FORECAST_QTY INT,
		M9_FORECAST_QTY INT,
		M10_FORECAST_QTY INT,
		M11_FORECAST_QTY INT,
		M12_FORECAST_QTY INT		
	)

	CREATE TABLE #RESULT_TABLE
	(
		FAB_PART_NUMBER VARCHAR(50),
		FG_PART_NUMBER VARCHAR(50), 
		CHIP_SET VARCHAR(50),
		Product_Line VARCHAR(50),
		Chip_Family VARCHAR(50),
		Process VARCHAR(50),
		FG_PS_ID INT,
		CQ_SHIPPED_QTY INT, --CURRENT QTR SHIPPED QTY
		CQ_FORECAST_QTY INT, --CURRENT QTR FORECAST QTY
		Q1_FORECAST_QTY INT, --NEXT QTR FORECAST QTY
		Q2_FORECAST_QTY INT, --NEXT NEXT QTR FORECAST QTY
		Q3_FORECAST_QTY INT, --NEXT NEXT NEXT QTR FORECAST QTY
		DELINQUENT_BACKLOG_QTY INT,
		CQ_BACKLOG_QTY INT,
		FG_MIN_LEVEL_QTY INT,
		FAB_OPEN_PO_QTY INT,
		FAB_QTY INT,
		WAFER_STORE_QTY INT,
		SORT_QTY INT,
		DIE_BANK_QTY INT,
		ASSEMBLY_QTY INT,
		PRE_TEST INT,
		TEST_QTY INT,
		EOL_QTY INT,
		FG_QTY INT,
		FAB_YIELD FLOAT,
		SORT_YIELD FLOAT,
		ASSEMBLY_YIELD FLOAT,
		TEST_YIELD FLOAT,
		Fab_CYCLE_TIME INT,
		Sort_CYCLE_TIME INT,
		Assembly_CYCLE_TIME INT,
		TEST_CYCLE_TIME INT,
		EOL_CYCLE_TIME INT,
		GROSS_DIE_PER_WAFER INT,
		Wafer_Cost FLOAT,
		DIES_PER_PACKAGE INT,
		M1_FORECAST_QTY INT, --CURRENT QTR START MONTH QTY
		M2_FORECAST_QTY INT, --NEXT MONTH FROM THE CURRENT QTR START MONTH QTY AND SO ON....
		M3_FORECAST_QTY INT,
		M4_FORECAST_QTY INT,
		M5_FORECAST_QTY INT,
		M6_FORECAST_QTY INT,
		M7_FORECAST_QTY INT,
		M8_FORECAST_QTY INT,
		M9_FORECAST_QTY INT,
		M10_FORECAST_QTY INT,
		M11_FORECAST_QTY INT,
		M12_FORECAST_QTY INT,
		ASSEMBLY_PART_NUMBER VARCHAR(50),
		ASSM_RELEASE_QTY INT,
		ASSEMBLY_RELEASE_QTY INT,
		M1_BACKLOG_QTY INT,
		M2_BACKLOG_QTY INT,
		M3_BACKLOG_QTY INT,
		M4_BACKLOG_QTY INT,
		M5_BACKLOG_QTY INT,
		M6_BACKLOG_QTY INT,
		M7_BACKLOG_QTY INT,
		M8_BACKLOG_QTY INT,
		M9_BACKLOG_QTY INT,
		M10_BACKLOG_QTY INT,
		M11_BACKLOG_QTY INT,
		M12_BACKLOG_QTY INT
	)

	DECLARE @CURR_QTR_START_DATE DATETIME
	DECLARE @CURR_QTR_END_DATE DATETIME
	DECLARE @CURR_QTR_NO VARCHAR(20)
	DECLARE @CURR_QTR INT
	DECLARE @CURR_YEAR INT
	DECLARE @NEXT_QTR INT
	DECLARE @NEXT_YEAR INT
	DECLARE @FORECAST_CURSOR CURSOR
	DECLARE @FG_PROCESSSTEP_ID INT
	DECLARE @PROCESSSTEP_CURSOR CURSOR
	DECLARE @PROCESSSTEP_ID INT
	DECLARE @PROCESS VARCHAR(50)
	DECLARE @PS_TYPE VARCHAR(50)
	DECLARE @PS_TYPE_NAME VARCHAR(50)
	DECLARE @Wafer_Cost FLOAT
	DECLARE @TFNAME VARCHAR(50)
	DECLARE @FC_MONTH INT
	DECLARE @FC_YEAR INT
	DECLARE @CNT INT
	DECLARE @FC_QRY VARCHAR(1000)
	DECLARE @FG_MIN_LEVEL_QTY INT

	SELECT @CURR_QTR_START_DATE = CQC_START_DATE, @CURR_QTR_END_DATE = CQC_END_DATE, @CURR_QTR_NO = CQC_QUARTER 
	FROM Company_Quarterly_Calendar WHERE CQC_START_DATE <= CURRENT_TIMESTAMP AND CQC_END_DATE  >= CURRENT_TIMESTAMP

	SET @CURR_QTR = CONVERT(INT, SUBSTRING ( @CURR_QTR_NO,len(@CURR_QTR_NO),1))
	SET @CURR_YEAR = YEAR(CURRENT_TIMESTAMP)

	SELECT @TFNAME = TFNAME FROM FCTFRAME WHERE TFSelect = 0

	IF( @TFNAME = 'MONTH' ) --MONTHLY FORECAST
	 BEGIN

		IF(@CURR_QTR = 1)
		 BEGIN
			SET @FC_MONTH = 0
		 END
		ELSE IF(@CURR_QTR = 2)
		 BEGIN
			SET @FC_MONTH = 3
		 END
		ELSE IF(@CURR_QTR = 3)
		 BEGIN
			SET @FC_MONTH = 6
		 END
		ELSE
		 BEGIN
			SET @FC_MONTH = 9
		 END
		
		SET @FC_YEAR = @CURR_YEAR
		SET @CNT = 1
		WHILE(@CNT <= 12)
		BEGIN
			SET @FC_QRY = N'INSERT INTO #FORECAST_TABLE (FG_PART_NUMBER, FG_PS_ID, M'+CONVERT(VARCHAR,@CNT)+'_FORECAST_QTY)
			SELECT P.PART_NUMBER, P.PROCESSSTEP_ID, SUM(QTY) FROM FCBUILDMASTER FC, PRODUCT P WHERE FC.PRODID = P.PRODUCT_ID AND FC.YEAR = ' + CONVERT(VARCHAR,@FC_YEAR) + ' AND FC.MONTH = ' + CONVERT(VARCHAR,@FC_MONTH) + ' 
			GROUP BY P.PROCESSSTEP_ID,PART_NUMBER'
		
			EXEC (@FC_QRY)
			SET @FC_MONTH = @FC_MONTH+1
			IF(@FC_MONTH > 11)
			 BEGIN
				SET @FC_MONTH = 0
				SET @FC_YEAR = @FC_YEAR + 1
			 END
			SET @CNT = @CNT + 1
		END

		INSERT INTO #FORECAST_TABLE ( FG_PART_NUMBER, FG_PS_ID, CQ_SHIPPED_QTY)
		SELECT P.Part_NUMBER, P.PROCESSSTEP_ID, SUM(CSLIT_QTY_SHIPPED) FROM
		CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P
		WHERE CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND COOL.PRODUCT_ID = P.PRODUCT_ID AND CSLIT_SHIPPED_DATE >= @CURR_QTR_START_DATE
		AND CSLIT_STATUS = 'SHIPPED'
		AND P.ProcessStep_ID IN ( SELECT DISTINCT FG_PS_ID FROM #FORECAST_TABLE )
		GROUP BY P.Part_NUMBER, P.PROCESSSTEP_ID
	 END
	ELSE --QUARTERLY FORECAST
	 BEGIN
		INSERT INTO #FORECAST_TABLE (FG_PART_NUMBER, FG_PS_ID, CQ_FORECAST_QTY)
		SELECT P.Part_NUMBER, P.PROCESSSTEP_ID, SUM(QTY) FROM FCBUILDMASTER FC, PRODUCT P WHERE FC.PRODID = P.PRODUCT_ID AND FC.YEAR = @CURR_YEAR AND QUARTER = @CURR_QTR
		GROUP BY P.PROCESSSTEP_ID,Part_NUMBER

		-- This is a dummy insert to get the orders that are having backlog but no forecast.
		INSERT INTO #FORECAST_TABLE ( FG_PART_NUMBER, FG_PS_ID, CQ_BACKLOG_QTY )
		SELECT P.Part_NUMBER, P.PROCESSSTEP_ID, SUM(COOL_Qty)-(ISNULL( (SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID  AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @CURR_QTR_END_DATE AND COOL_STATUS <> 'SHIPPED' ) , 0 )) FROM
		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P
		WHERE COOL.PRODUCT_ID = P.PRODUCT_ID AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @CURR_QTR_END_DATE AND COOL_STATUS <> 'SHIPPED'
		GROUP BY P.Part_NUMBER, P.PROCESSSTEP_ID

		--SELECT @M1_BACKLOG_QTY = SUM(COOL_Qty)-ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @BKL_END_DATE ), 0) FROM Customer_Open_Order_LineItem COOL, Product P WHERE COOL.Product_ID = P.Product_ID AND COOL_STATUS <> 'SHIPPED' AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @BKL_END_DATE

		INSERT INTO #FORECAST_TABLE ( FG_PART_NUMBER, FG_PS_ID, CQ_SHIPPED_QTY)
		SELECT P.Part_NUMBER, P.PROCESSSTEP_ID, SUM(CSLIT_QTY_SHIPPED) FROM
		CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P
		WHERE CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND COOL.PRODUCT_ID = P.PRODUCT_ID AND CSLIT_SHIPPED_DATE >= @CURR_QTR_START_DATE
		GROUP BY P.Part_NUMBER, P.PROCESSSTEP_ID

		IF( @CURR_QTR + 1 > 4 )
		BEGIN
			SET @NEXT_QTR = ( @CURR_QTR + 1 ) - 4
			SET @NEXT_YEAR = @CURR_YEAR + 1
		END
		ELSE
		BEGIN
			SET @NEXT_QTR = @CURR_QTR + 1
			SET @NEXT_YEAR = @CURR_YEAR
		END

		INSERT INTO #FORECAST_TABLE (FG_PART_NUMBER, FG_PS_ID, Q1_FORECAST_QTY)
		SELECT Part_NUMBER, P.PROCESSSTEP_ID, SUM(QTY) FROM FCBUILDMASTER FC, PRODUCT P WHERE FC.PRODID = P.PRODUCT_ID AND FC.YEAR = @NEXT_YEAR AND QUARTER = @NEXT_QTR
		GROUP BY P.PROCESSSTEP_ID,Part_NUMBER

		IF( @CURR_QTR + 2 > 4 )
		BEGIN
			SET @NEXT_QTR = ( @CURR_QTR + 2 ) - 4
			SET @NEXT_YEAR = @CURR_YEAR + 1
		END
		ELSE
		BEGIN
			SET @NEXT_QTR = @CURR_QTR + 2
			SET @NEXT_YEAR = @CURR_YEAR
		END

		INSERT INTO #FORECAST_TABLE ( FG_PART_NUMBER, FG_PS_ID, Q2_FORECAST_QTY)
		SELECT Part_NUMBER, P.PROCESSSTEP_ID, SUM(QTY) FROM FCBUILDMASTER FC, PRODUCT P WHERE FC.PRODID = P.PRODUCT_ID AND FC.YEAR = @NEXT_YEAR AND QUARTER = @NEXT_QTR
		GROUP BY P.PROCESSSTEP_ID,Part_NUMBER
		
		IF( @CURR_QTR + 3 > 4 )
		BEGIN
			SET @NEXT_QTR = ( @CURR_QTR + 3 ) - 4
			SET @NEXT_YEAR = @CURR_YEAR + 1
		END
		ELSE
		BEGIN
			SET @NEXT_QTR = @CURR_QTR + 3
			SET @NEXT_YEAR = @CURR_YEAR
		END

		INSERT INTO #FORECAST_TABLE ( FG_PART_NUMBER, FG_PS_ID, Q3_FORECAST_QTY)
		SELECT Part_NUMBER, P.PROCESSSTEP_ID, SUM(QTY) FROM FCBUILDMASTER FC, PRODUCT P WHERE FC.PRODID = P.PRODUCT_ID AND FC.YEAR = @NEXT_YEAR AND QUARTER = @NEXT_QTR
		GROUP BY P.PROCESSSTEP_ID,Part_NUMBER
	 END
	INSERT INTO #RESULT_TABLE ( FG_PART_NUMBER, FG_PS_ID, CQ_SHIPPED_QTY, CQ_FORECAST_QTY, Q1_FORECAST_QTY, Q2_FORECAST_QTY, Q3_FORECAST_QTY, M1_FORECAST_QTY, M2_FORECAST_QTY, M3_FORECAST_QTY, M4_FORECAST_QTY, M5_FORECAST_QTY, M6_FORECAST_QTY, M7_FORECAST_QTY, M8_FORECAST_QTY, M9_FORECAST_QTY, M10_FORECAST_QTY, M11_FORECAST_QTY, M12_FORECAST_QTY )
	SELECT FG_PART_NUMBER, FG_PS_ID, SUM(CQ_SHIPPED_QTY), SUM(CQ_FORECAST_QTY), SUM(Q1_FORECAST_QTY), SUM(Q2_FORECAST_QTY), SUM(Q3_FORECAST_QTY), SUM(M1_FORECAST_QTY), SUM(M2_FORECAST_QTY), SUM(M3_FORECAST_QTY), SUM(M4_FORECAST_QTY), SUM(M5_FORECAST_QTY), SUM(M6_FORECAST_QTY), SUM(M7_FORECAST_QTY), SUM(M8_FORECAST_QTY), SUM(M9_FORECAST_QTY), SUM(M10_FORECAST_QTY), SUM(M11_FORECAST_QTY), SUM(M12_FORECAST_QTY) FROM #FORECAST_TABLE
	GROUP BY FG_PART_NUMBER, FG_PS_ID
	
	DECLARE @FG_PS_ID_CURSOR CURSOR
	DECLARe @BOM_PS_ID_CURSOR CURSOR
	DECLARE @BOM_PROCESSSTEP_ID INT
	DECLARE @BOM_PART_NUMBER VARCHAR(50)
	DECLARE @BOM_MIN_LEVEL_QTY INT
	DECLARE @CQ_SHIPPED_QTY INT
	DECLARE @CQ_FORECAST_QTY INT
	DECLARE @Q1_FORECAST_QTY INT
	DECLARE @Q2_FORECAST_QTY INT
	DECLARE @Q3_FORECAST_QTY INT
	DECLARE @M1_FORECAST_QTY INT
	DECLARE @M2_FORECAST_QTY INT
	DECLARE @M3_FORECAST_QTY INT
	DECLARE @M4_FORECAST_QTY INT
	DECLARE @M5_FORECAST_QTY INT
	DECLARE @M6_FORECAST_QTY INT
	DECLARE @M7_FORECAST_QTY INT
	DECLARE @M8_FORECAST_QTY INT
	DECLARE @M9_FORECAST_QTY INT
	DECLARE @M10_FORECAST_QTY INT
	DECLARE @M11_FORECAST_QTY INT
	DECLARE @M12_FORECAST_QTY INT
	-----INSERT BOM PARTS FOR FG'S------
	INSERT INTO #RESULT_TABLE ( FG_PART_NUMBER, FG_PS_ID, CQ_SHIPPED_QTY, CQ_FORECAST_QTY, Q1_FORECAST_QTY, Q2_FORECAST_QTY, Q3_FORECAST_QTY, M1_FORECAST_QTY, M2_FORECAST_QTY, M3_FORECAST_QTY, M4_FORECAST_QTY, M5_FORECAST_QTY, M6_FORECAST_QTY, M7_FORECAST_QTY, M8_FORECAST_QTY, M9_FORECAST_QTY, M10_FORECAST_QTY, M11_FORECAST_QTY, M12_FORECAST_QTY )
	SELECT P.PART_NUMBER, BOM_PROCESSSTEP_ID, SUM(CQ_SHIPPED_QTY), SUM(CQ_FORECAST_QTY), SUM(Q1_FORECAST_QTY), SUM(Q2_FORECAST_QTY), SUM(Q3_FORECAST_QTY), SUM(M1_FORECAST_QTY), SUM(M2_FORECAST_QTY), SUM(M3_FORECAST_QTY), SUM(M4_FORECAST_QTY), SUM(M5_FORECAST_QTY), SUM(M6_FORECAST_QTY), SUM(M7_FORECAST_QTY), SUM(M8_FORECAST_QTY), SUM(M9_FORECAST_QTY), SUM(M10_FORECAST_QTY), SUM(M11_FORECAST_QTY), SUM(M12_FORECAST_QTY) FROM #FORECAST_TABLE, ASSEMBLY_BOM_SETUP_TABLE, PRODUCT P
	WHERE ASSEMBLY_PROCESSSTEP_ID = FG_PS_ID AND P.PROCESSSTEP_ID = BOM_PROCESSSTEP_ID
	GROUP BY P.PART_NUMBER, BOM_PROCESSSTEP_ID

	SET @FG_PS_ID_CURSOR = CURSOR FOR SELECT FG_PS_ID FROM #RESULT_TABLE
	OPEN @FG_PS_ID_CURSOR
	FETCH NEXT FROM @FG_PS_ID_CURSOR INTO @FG_PROCESSSTEP_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN

		-- UPDATING FG MIN LEVEL QUANTITY FOR EVERY PRODUCT HERE. NEXT STEP IS ONLY FOR ADDING MIN LEVEL QUANTITY FOR BOM PRODUCTS.
		SELECT @FG_MIN_LEVEL_QTY = Parameter_Value FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Reorder Level' AND PROCESSSTEP_ID = @FG_PROCESSSTEP_ID
		UPDATE #RESULT_TABLE SET FG_MIN_LEVEL_QTY = ISNULL(@FG_MIN_LEVEL_QTY, 0) WHERE FG_PS_ID = @FG_PROCESSSTEP_ID

		SET @BOM_PS_ID_CURSOR = CURSOR FOR SELECT BOM_PROCESSSTEP_ID, P.PART_NUMBER FROM ASSEMBLY_BOM_SETUP_TABLE, PRODUCT P WHERE P.PROCESSSTEP_ID = BOM_PROCESSSTEP_ID AND ASSEMBLY_PROCESSSTEP_ID = @FG_PROCESSSTEP_ID
		OPEN @BOM_PS_ID_CURSOR 
		FETCH NEXT FROM @BOM_PS_ID_CURSOR INTO @BOM_PROCESSSTEP_ID, @BOM_PART_NUMBER
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF((SELECT COUNT(*) FROM #RESULT_TABLE WHERE FG_PS_ID = @BOM_PROCESSSTEP_ID) = 0) --NEED TO INSERT
			BEGIN
				INSERT INTO #RESULT_TABLE ( FG_PART_NUMBER, FG_PS_ID, CQ_SHIPPED_QTY, CQ_FORECAST_QTY, Q1_FORECAST_QTY, Q2_FORECAST_QTY, Q3_FORECAST_QTY, M1_FORECAST_QTY, M2_FORECAST_QTY, M3_FORECAST_QTY, M4_FORECAST_QTY, M5_FORECAST_QTY, M6_FORECAST_QTY, M7_FORECAST_QTY, M8_FORECAST_QTY, M9_FORECAST_QTY, M10_FORECAST_QTY, M11_FORECAST_QTY, M12_FORECAST_QTY, FG_MIN_LEVEL_QTY )
				SELECT @BOM_PART_NUMBER, @BOM_PROCESSSTEP_ID, CQ_SHIPPED_QTY, CQ_FORECAST_QTY, Q1_FORECAST_QTY, Q2_FORECAST_QTY, Q3_FORECAST_QTY, M1_FORECAST_QTY, M2_FORECAST_QTY, M3_FORECAST_QTY, M4_FORECAST_QTY, M5_FORECAST_QTY, M6_FORECAST_QTY, M7_FORECAST_QTY, M8_FORECAST_QTY, M9_FORECAST_QTY, M10_FORECAST_QTY, M11_FORECAST_QTY, M12_FORECAST_QTY, @FG_MIN_LEVEL_QTY FROM #RESULT_TABLE
				WHERE FG_PS_ID = @FG_PROCESSSTEP_ID
			END
			ELSE --NEED TO UPDATE
			BEGIN
				SET @CQ_SHIPPED_QTY = 0
				SET @CQ_FORECAST_QTY = 0
				SET @Q1_FORECAST_QTY = 0
				SET @Q2_FORECAST_QTY = 0
				SET @Q3_FORECAST_QTY = 0
				SET @M1_FORECAST_QTY = 0
				SET @M2_FORECAST_QTY = 0
				SET @M3_FORECAST_QTY = 0
				SET @M4_FORECAST_QTY = 0
				SET @M5_FORECAST_QTY = 0
				SET @M6_FORECAST_QTY = 0
				SET @M7_FORECAST_QTY = 0
				SET @M8_FORECAST_QTY = 0
				SET @M9_FORECAST_QTY = 0
				SET @M10_FORECAST_QTY = 0
				SET @M11_FORECAST_QTY = 0
				SET @M12_FORECAST_QTY = 0
				SET @BOM_MIN_LEVEL_QTY = 0

				SELECT @CQ_SHIPPED_QTY=CQ_SHIPPED_QTY, @CQ_FORECAST_QTY=CQ_FORECAST_QTY, @Q1_FORECAST_QTY=Q1_FORECAST_QTY, @Q2_FORECAST_QTY=Q2_FORECAST_QTY, @Q3_FORECAST_QTY=Q3_FORECAST_QTY, @M1_FORECAST_QTY=M1_FORECAST_QTY, @M2_FORECAST_QTY=M2_FORECAST_QTY, @M3_FORECAST_QTY=M3_FORECAST_QTY, @M4_FORECAST_QTY=M4_FORECAST_QTY, @M5_FORECAST_QTY=M5_FORECAST_QTY, @M6_FORECAST_QTY=M6_FORECAST_QTY, @M7_FORECAST_QTY=M7_FORECAST_QTY, @M8_FORECAST_QTY=M8_FORECAST_QTY, @M9_FORECAST_QTY=M9_FORECAST_QTY, @M10_FORECAST_QTY=M10_FORECAST_QTY, @M11_FORECAST_QTY=M11_FORECAST_QTY, @M12_FORECAST_QTY=M12_FORECAST_QTY, @BOM_MIN_LEVEL_QTY = FG_MIN_LEVEL_QTY FROM #RESULT_TABLE
				WHERE FG_PS_ID = @FG_PROCESSSTEP_ID

				UPDATE #RESULT_TABLE SET CQ_SHIPPED_QTY=ISNULL(CQ_SHIPPED_QTY, 0)+ISNULL(@CQ_SHIPPED_QTY, 0),
										CQ_FORECAST_QTY=ISNULL(CQ_FORECAST_QTY, 0)+ISNULL(@CQ_FORECAST_QTY, 0),
										Q1_FORECAST_QTY=ISNULL(Q1_FORECAST_QTY, 0)+ISNULL(@Q1_FORECAST_QTY, 0),
										Q2_FORECAST_QTY=ISNULL(Q2_FORECAST_QTY, 0)+ISNULL(@Q2_FORECAST_QTY, 0),
										Q3_FORECAST_QTY=ISNULL(Q3_FORECAST_QTY, 0)+ISNULL(@Q3_FORECAST_QTY, 0),
										M1_FORECAST_QTY=ISNULL(M1_FORECAST_QTY, 0)+ISNULL(@M1_FORECAST_QTY, 0),
										M2_FORECAST_QTY=ISNULL(M2_FORECAST_QTY, 0)+ISNULL(@M2_FORECAST_QTY, 0),
										M3_FORECAST_QTY=ISNULL(M3_FORECAST_QTY, 0)+ISNULL(@M3_FORECAST_QTY, 0),
										M4_FORECAST_QTY=ISNULL(M4_FORECAST_QTY, 0)+ISNULL(@M4_FORECAST_QTY, 0),
										M5_FORECAST_QTY=ISNULL(M5_FORECAST_QTY, 0)+ISNULL(@M5_FORECAST_QTY, 0),
										M6_FORECAST_QTY=ISNULL(M6_FORECAST_QTY, 0)+ISNULL(@M6_FORECAST_QTY, 0),
										M7_FORECAST_QTY=ISNULL(M7_FORECAST_QTY, 0)+ISNULL(@M7_FORECAST_QTY, 0),
										M8_FORECAST_QTY=ISNULL(M8_FORECAST_QTY, 0)+ISNULL(@M8_FORECAST_QTY, 0),
										M9_FORECAST_QTY=ISNULL(M9_FORECAST_QTY, 0)+ISNULL(@M9_FORECAST_QTY, 0),
										M10_FORECAST_QTY=ISNULL(M10_FORECAST_QTY, 0)+ISNULL(@M10_FORECAST_QTY, 0),
										M11_FORECAST_QTY=ISNULL(M11_FORECAST_QTY, 0)+ISNULL(@M11_FORECAST_QTY, 0),
										M12_FORECAST_QTY=ISNULL(M12_FORECAST_QTY, 0)+ISNULL(@M12_FORECAST_QTY, 0),
										FG_MIN_LEVEL_QTY=ISNULL(FG_MIN_LEVEL_QTY, 0)+ISNULL(@FG_MIN_LEVEL_QTY, 0)
				WHERE FG_PS_ID = @BOM_PROCESSSTEP_ID
			END
			SET @BOM_PROCESSSTEP_ID = 0
			SET @BOM_PART_NUMBER = NULL
			FETCH NEXT FROM @BOM_PS_ID_CURSOR INTO @BOM_PROCESSSTEP_ID, @BOM_PART_NUMBER
		END
		CLOSE @BOM_PS_ID_CURSOR
		DEALLOCATE @BOM_PS_ID_CURSOR

		SET @FG_PROCESSSTEP_ID = 0
		FETCH NEXT FROM @FG_PS_ID_CURSOR INTO @FG_PROCESSSTEP_ID
	END
	CLOSE @FG_PS_ID_CURSOR
	DEALLOCATE @FG_PS_ID_CURSOR
	SET @FG_PROCESSSTEP_ID = 0
	---------------------------------------------------------------

	-----INSERT ASSEMBLY BOM PARTS FOR FG'S------
	
	SET @FG_PS_ID_CURSOR = CURSOR FOR SELECT FG_PS_ID FROM #RESULT_TABLE
	OPEN @FG_PS_ID_CURSOR
	FETCH NEXT FROM @FG_PS_ID_CURSOR INTO @FG_PROCESSSTEP_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- FETCH FG MIN LEVEL QUANTITY FOR THE FG PRODUCT
		SELECT @FG_MIN_LEVEL_QTY = FG_MIN_LEVEL_QTY FROM #RESULT_TABLE WHERE FG_PS_ID = @FG_PROCESSSTEP_ID
		
		SET @BOM_PS_ID_CURSOR = CURSOR FOR SELECT BOM_PROCESSSTEP_ID, P.PART_NUMBER FROM ASSEMBLY_BOM_SETUP_TABLE, PRODUCT P WHERE P.PROCESSSTEP_ID = BOM_PROCESSSTEP_ID AND ASSEMBLY_PROCESSSTEP_ID IN (SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @FG_PROCESSSTEP_ID, 0 )  WHERE PS_TYPE_NAME = 'ASSEMBLY')
		OPEN @BOM_PS_ID_CURSOR 
		FETCH NEXT FROM @BOM_PS_ID_CURSOR INTO @BOM_PROCESSSTEP_ID, @BOM_PART_NUMBER
		WHILE @@FETCH_STATUS = 0
		BEGIN

				SET @CQ_SHIPPED_QTY = 0
				SET @CQ_FORECAST_QTY = 0
				SET @Q1_FORECAST_QTY = 0
				SET @Q2_FORECAST_QTY = 0
				SET @Q3_FORECAST_QTY = 0
				SET @M1_FORECAST_QTY = 0
				SET @M2_FORECAST_QTY = 0
				SET @M3_FORECAST_QTY = 0
				SET @M4_FORECAST_QTY = 0
				SET @M5_FORECAST_QTY = 0
				SET @M6_FORECAST_QTY = 0
				SET @M7_FORECAST_QTY = 0
				SET @M8_FORECAST_QTY = 0
				SET @M9_FORECAST_QTY = 0
				SET @M10_FORECAST_QTY = 0
				SET @M11_FORECAST_QTY = 0
				SET @M12_FORECAST_QTY = 0
		
			IF((SELECT COUNT(*) FROM #RESULT_TABLE WHERE FG_PS_ID = @BOM_PROCESSSTEP_ID) = 0) --NEED TO INSERT
			BEGIN
				INSERT INTO #RESULT_TABLE ( FG_PART_NUMBER, FG_PS_ID, CQ_SHIPPED_QTY, CQ_FORECAST_QTY, Q1_FORECAST_QTY, Q2_FORECAST_QTY, Q3_FORECAST_QTY, M1_FORECAST_QTY, M2_FORECAST_QTY, M3_FORECAST_QTY, M4_FORECAST_QTY, M5_FORECAST_QTY, M6_FORECAST_QTY, M7_FORECAST_QTY, M8_FORECAST_QTY, M9_FORECAST_QTY, M10_FORECAST_QTY, M11_FORECAST_QTY, M12_FORECAST_QTY, FG_MIN_LEVEL_QTY )
				SELECT @BOM_PART_NUMBER, @BOM_PROCESSSTEP_ID, ISNULL( CQ_SHIPPED_QTY, 0), ISNULL( CQ_FORECAST_QTY, 0), ISNULL( Q1_FORECAST_QTY, 0), ISNULL( Q2_FORECAST_QTY, 0), ISNULL( Q3_FORECAST_QTY, 0), ISNULL( M1_FORECAST_QTY, 0), ISNULL( M2_FORECAST_QTY, 0), ISNULL( M3_FORECAST_QTY, 0), ISNULL( M4_FORECAST_QTY, 0), ISNULL( M5_FORECAST_QTY, 0), ISNULL( M6_FORECAST_QTY, 0), ISNULL( M7_FORECAST_QTY, 0), ISNULL( M8_FORECAST_QTY, 0), ISNULL( M9_FORECAST_QTY, 0), ISNULL( M10_FORECAST_QTY, 0), ISNULL( M11_FORECAST_QTY, 0), ISNULL( M12_FORECAST_QTY, 0), ISNULL(@FG_MIN_LEVEL_QTY, 0) FROM #RESULT_TABLE
				WHERE FG_PS_ID = @FG_PROCESSSTEP_ID
			END
			ELSE --NEED TO UPDATE
			BEGIN


				SELECT @CQ_SHIPPED_QTY=CQ_SHIPPED_QTY, @CQ_FORECAST_QTY=CQ_FORECAST_QTY, @Q1_FORECAST_QTY=Q1_FORECAST_QTY, @Q2_FORECAST_QTY=Q2_FORECAST_QTY, @Q3_FORECAST_QTY=Q3_FORECAST_QTY, @M1_FORECAST_QTY=M1_FORECAST_QTY, @M2_FORECAST_QTY=M2_FORECAST_QTY, @M3_FORECAST_QTY=M3_FORECAST_QTY, @M4_FORECAST_QTY=M4_FORECAST_QTY, @M5_FORECAST_QTY=M5_FORECAST_QTY, @M6_FORECAST_QTY=M6_FORECAST_QTY, @M7_FORECAST_QTY=M7_FORECAST_QTY, @M8_FORECAST_QTY=M8_FORECAST_QTY, @M9_FORECAST_QTY=M9_FORECAST_QTY, @M10_FORECAST_QTY=M10_FORECAST_QTY, @M11_FORECAST_QTY=M11_FORECAST_QTY, @M12_FORECAST_QTY=M12_FORECAST_QTY FROM #RESULT_TABLE
				WHERE FG_PS_ID = @FG_PROCESSSTEP_ID

				IF @BOM_PROCESSSTEP_ID = 66
				BEGIN
					print @FG_PROCESSSTEP_ID
					print @M3_FORECAST_QTY
				END

				UPDATE #RESULT_TABLE SET CQ_SHIPPED_QTY=ISNULL(CQ_SHIPPED_QTY, 0)+ISNULL(@CQ_SHIPPED_QTY, 0),
										CQ_FORECAST_QTY=ISNULL(CQ_FORECAST_QTY, 0)+ISNULL(@CQ_FORECAST_QTY, 0),
										Q1_FORECAST_QTY=ISNULL(Q1_FORECAST_QTY, 0)+ISNULL(@Q1_FORECAST_QTY, 0),
										Q2_FORECAST_QTY=ISNULL(Q2_FORECAST_QTY, 0)+ISNULL(@Q2_FORECAST_QTY, 0),
										Q3_FORECAST_QTY=ISNULL(Q3_FORECAST_QTY, 0)+ISNULL(@Q3_FORECAST_QTY, 0),
										M1_FORECAST_QTY=ISNULL(M1_FORECAST_QTY, 0)+ISNULL(@M1_FORECAST_QTY, 0),
										M2_FORECAST_QTY=ISNULL(M2_FORECAST_QTY, 0)+ISNULL(@M2_FORECAST_QTY, 0),
										M3_FORECAST_QTY=ISNULL(M3_FORECAST_QTY, 0)+ISNULL(@M3_FORECAST_QTY, 0),
										M4_FORECAST_QTY=ISNULL(M4_FORECAST_QTY, 0)+ISNULL(@M4_FORECAST_QTY, 0),
										M5_FORECAST_QTY=ISNULL(M5_FORECAST_QTY, 0)+ISNULL(@M5_FORECAST_QTY, 0),
										M6_FORECAST_QTY=ISNULL(M6_FORECAST_QTY, 0)+ISNULL(@M6_FORECAST_QTY, 0),
										M7_FORECAST_QTY=ISNULL(M7_FORECAST_QTY, 0)+ISNULL(@M7_FORECAST_QTY, 0),
										M8_FORECAST_QTY=ISNULL(M8_FORECAST_QTY, 0)+ISNULL(@M8_FORECAST_QTY, 0),
										M9_FORECAST_QTY=ISNULL(M9_FORECAST_QTY, 0)+ISNULL(@M9_FORECAST_QTY, 0),
										M10_FORECAST_QTY=ISNULL(M10_FORECAST_QTY, 0)+ISNULL(@M10_FORECAST_QTY, 0),
										M11_FORECAST_QTY=ISNULL(M11_FORECAST_QTY, 0)+ISNULL(@M11_FORECAST_QTY, 0),
										M12_FORECAST_QTY=ISNULL(M12_FORECAST_QTY, 0)+ISNULL(@M12_FORECAST_QTY, 0),
										FG_MIN_LEVEL_QTY=ISNULL(FG_MIN_LEVEL_QTY, 0)+ISNULL(@FG_MIN_LEVEL_QTY, 0)
				WHERE FG_PS_ID = @BOM_PROCESSSTEP_ID
			END
			SET @BOM_PROCESSSTEP_ID = 0
			SET @BOM_PART_NUMBER = NULL
			FETCH NEXT FROM @BOM_PS_ID_CURSOR INTO @BOM_PROCESSSTEP_ID, @BOM_PART_NUMBER
		END
		CLOSE @BOM_PS_ID_CURSOR
		DEALLOCATE @BOM_PS_ID_CURSOR

		SET @FG_PROCESSSTEP_ID = 0
		FETCH NEXT FROM @FG_PS_ID_CURSOR INTO @FG_PROCESSSTEP_ID
	END
	CLOSE @FG_PS_ID_CURSOR
	DEALLOCATE @FG_PS_ID_CURSOR
	SET @FG_PROCESSSTEP_ID = 0

--select * from #RESULT_TABLE
---------------------------------------------------------------
	DECLARE @FAB_QTY INT
	DECLARE @WAFERSTORE_QTY INT
	DECLARE @SORT_QTY INT
	DECLARE @DIEBANK_QTY INT
	DECLARE @ASSEMBLY_QTY INT
	DECLARE @ASSEMBLY_RELEASE_QTY INT
	DECLARE @PRETEST_QTY INT
	DECLARE @TEST_QTY INT
	DECLARE @EOL_QTY INT
	DECLARE @FG_QTY INT
	DECLARE @TRANSIT_QTY INT

	DECLARE @GROSS_DIE_PER_WAFER INT
	DECLARE @FAB_PART_NUMBER VARCHAR(50)
	DECLARE @ASSEMBLY_PART_NUMBER VARCHAR(50)
	DECLARE @FAB_YIELD FLOAT
	DECLARE @SORT_YIELD FLOAT
	DECLARE @ASSEMBLY_YIELD FLOAT
	DECLARE @TEST_YIELD FLOAT
	DECLARE @CHIPSET VARCHAR(50)
	DECLARE @FAB_CYCLE_TIME INT	
	DECLARE @SORT_CYCLE_TIME INT	
	DECLARE @ASSEMBLY_CYCLE_TIME INT	
	DECLARE @TEST_CYCLE_TIME INT	
	DECLARE @EOL_CYCLE_TIME INT	
	DECLARE @OPEN_PO_QTY INT
	DECLARE @CHIP_FAMILY VARCHAR(50)
	DECLARE @PRODUCT_LINE VARCHAR(50)
	DECLARE @CQ_BACKLOG_QTY INT
	DECLARE @DIES_PER_PACKAGE INT
	DECLARE @CURRENT_QTR_START_DATE DATETIME
	DECLARE @BKL_START_DATE DATETIME
	DECLARE @BKL_END_DATE DATETIME
	DECLARE @DELINQUENT_BACKLOG_QTY INT
	DECLARE @M1_BACKLOG_QTY INT
	DECLARE @M2_BACKLOG_QTY INT
	DECLARE @M3_BACKLOG_QTY INT
	DECLARE @M4_BACKLOG_QTY INT
	DECLARE @M5_BACKLOG_QTY INT
	DECLARE @M6_BACKLOG_QTY INT
	DECLARE @M7_BACKLOG_QTY INT
	DECLARE @M8_BACKLOG_QTY INT
	DECLARE @M9_BACKLOG_QTY INT
	DECLARE @M10_BACKLOG_QTY INT
	DECLARE @M11_BACKLOG_QTY INT
	DECLARE @M12_BACKLOG_QTY INT

	SELECT @CURRENT_QTR_START_DATE = CQC_START_DATE FROM COMPANY_QUARTERLY_CALENDAR WHERE CQC_START_DATE <= CURRENT_TIMESTAMP AND CQC_END_DATE >= CURRENT_TIMESTAMP

	SET	@FORECAST_CURSOR = CURSOR FOR SELECT FG_PS_ID FROM #RESULT_TABLE
	OPEN @FORECAST_CURSOR
	FETCH NEXT FROM @FORECAST_CURSOR INTO @FG_PROCESSSTEP_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @FAB_QTY = 0
		SET @WAFERSTORE_QTY = 0
		SET @SORT_QTY = 0
		SET @DIEBANK_QTY = 0
		SET @ASSEMBLY_QTY = 0
		SET @PRETEST_QTY = 0
		SET @TEST_QTY = 0
		SET @EOL_QTY = 0
		SET @FG_QTY = 0
		SET @GROSS_DIE_PER_WAFER = 0
		SET @FAB_PART_NUMBER = ''
		SET @FAB_YIELD = 0.0
		SET @SORT_YIELD = 0.0
		SET @ASSEMBLY_YIELD = 0.0
		SET @TEST_YIELD = 0.0
		SET @FAB_CYCLE_TIME = 0	
		SET @SORT_CYCLE_TIME = 0		
		SET @ASSEMBLY_CYCLE_TIME = 0		
		SET @TEST_CYCLE_TIME = 0		
		SET @EOL_CYCLE_TIME = 0		
		SET @OPEN_PO_QTY = 0
		SET @CHIPSET = ''
		SET @CHIP_FAMILY = ''
		SET @PRODUCT_LINE = ''
		SET @DIES_PER_PACKAGE = 1
		SET @FG_MIN_LEVEL_QTY  = 0
		SET @DELINQUENT_BACKLOG_QTY = 0
		SET @M1_BACKLOG_QTY = 0
		SET @M2_BACKLOG_QTY = 0
		SET @M3_BACKLOG_QTY = 0
		SET @M4_BACKLOG_QTY = 0
		SET @M5_BACKLOG_QTY = 0
		SET @M6_BACKLOG_QTY = 0
		SET @M7_BACKLOG_QTY = 0
		SET @M8_BACKLOG_QTY = 0
		SET @M9_BACKLOG_QTY = 0
		SET @M10_BACKLOG_QTY = 0
		SET @M11_BACKLOG_QTY = 0
		SET @M12_BACKLOG_QTY = 0


		SET @PROCESSSTEP_CURSOR = CURSOR FOR SELECT ProcessStep_Id, PS_Type, PS_Type_Name FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @FG_PROCESSSTEP_ID, 0 )
		OPEN @PROCESSSTEP_CURSOR
		FETCH NEXT FROM @PROCESSSTEP_CURSOR INTO @PROCESSSTEP_ID, @PS_TYPE, @PS_TYPE_NAME
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @TRANSIT_QTY = 0
			IF (@PS_TYPE = 'Fab' )-- OR @PS_TYPE_NAME = 'Wafer Store' OR @PS_TYPE_NAME = 'SORT')
			BEGIN
				
				SELECT @FAB_PART_NUMBER = PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @FAB_QTY = SUM(WAFER_QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @PROCESS = Parameter_Value FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Fab Process' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @Wafer_Cost = ISNULL( CONVERT( FLOAT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Unit Cost' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SET @FAB_QTY = ISNULL(@FAB_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
				-- HARD CODED TO TELEFUNKEN LOCATION AS WE ARE RECEVING PO INFORMATION ONLY FROM TELEFUNKEN
				IF( (SELECT COUNT(*) FROM Process_Step_Location_Table WHERE ProcessStep_ID = @PROCESSSTEP_ID AND Location_ID = 8) > 0 )
				BEGIN
					SELECT @OPEN_PO_QTY = SUM(Requested_Qty)-SUM(Received_Qty) FROM PurchaseOrder_Line_Item_Table WHERE ProcessStep_Id = @PROCESSSTEP_ID AND Status = 'APPROVED'
				END
				ELSE
				BEGIN
					SET @OPEN_PO_QTY = @FAB_QTY
				END
			
				SET @OPEN_PO_QTY = ISNULL( @OPEN_PO_QTY, 0 ) - @FAB_QTY
				/*IF @OPEN_PO_QTY < 0
				BEGIN
					SET @OPEN_PO_QTY = 0
				END*/
				SELECT @GROSS_DIE_PER_WAFER = ISNULL( CONVERT( INT, Parameter_Value), 1) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Gross Die/Wafer' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @FAB_CYCLE_TIME = ISNULL( CONVERT( INT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @FAB_YIELD = MIN(ISNULL( CASE WHEN @CURR_QTR = 1 THEN Q1_YIELD WHEN @CURR_QTR = 2 THEN Q2_YIELD WHEN @CURR_QTR = 3 THEN Q3_YIELD ELSE Q4_YIELD END, 100.00)) FROM UPDATE_VIEW_COST_TABLE  WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(FIN_YEAR) = @CURR_YEAR
				IF ( @FAB_YIELD IS NULL OR @FAB_YIELD <= 0.0 )
				BEGIN
					SET @FAB_YIELD = 100.00
				END
			END 
			ELSE IF (@PS_TYPE = 'Wafer Bank' )
			BEGIN
				SELECT @WAFERSTORE_QTY = SUM(WAFER_QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SET @WAFERSTORE_QTY = ISNULL(@WAFERSTORE_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
			END
			ELSE IF (@PS_TYPE = 'SORT' )
			BEGIN
				--SELECT @SORT_YIELD = MIN(ISNULL( CASE WHEN @CURR_QTR = 1 THEN Q1_YIELD WHEN @CURR_QTR = 2 THEN Q2_YIELD WHEN @CURR_QTR = 3 THEN Q3_YIELD ELSE Q4_YIELD END, 100.00)) FROM UPDATE_VIEW_COST_TABLE  WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(FIN_YEAR) = @CURR_YEAR
				SELECT @SORT_YIELD = ISNULL( CONVERT( FLOAT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Target Yield' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @SORT_QTY = SUM(WAFER_QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @SORT_CYCLE_TIME = ISNULL( CONVERT( INT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SET @SORT_QTY = ISNULL(@SORT_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
				IF ( @SORT_YIELD IS NULL OR @SORT_YIELD <= 0.0 )
				BEGIN
					SET @SORT_YIELD = 100.00
				END
			END
			ELSE IF (@PS_TYPE = 'Die Bank' )
			BEGIN
				SELECT @DIEBANK_QTY = SUM(QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SET @DIEBANK_QTY = ISNULL(@DIEBANK_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
			END
			ELSE IF (@PS_TYPE = 'Assembly' )
			BEGIN
				--SELECT @ASSEMBLY_YIELD = MIN(ISNULL( CASE WHEN @CURR_QTR = 1 THEN Q1_YIELD WHEN @CURR_QTR = 2 THEN Q2_YIELD WHEN @CURR_QTR = 3 THEN Q3_YIELD ELSE Q4_YIELD END, 100.00)) FROM UPDATE_VIEW_COST_TABLE  WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(FIN_YEAR) = @CURR_YEAR
				SELECT @ASSEMBLY_YIELD = ISNULL( CONVERT( FLOAT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Target Yield' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @DIES_PER_PACKAGE = ISNULL( CONVERT( INT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Dies/Package' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @ASSEMBLY_QTY = SUM(QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @ASSEMBLY_CYCLE_TIME = ISNULL( CONVERT( INT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SET @ASSEMBLY_QTY = ISNULL(@ASSEMBLY_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
				SELECT @ASSEMBLY_RELEASE_QTY = SUM(ARLT.QTY_PULLED) FROM ASSM_RELEASE_LOT_TABLE ARLT, ASSM_RELEASE_TABLE ART WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID AND ART.STATUS = 'APPROVED' AND ART.PROCESSSTEP_ID = @PROCESSSTEP_ID
				
				IF ( @ASSEMBLY_YIELD IS NOT NULL AND @ASSEMBLY_YIELD <= 1.0 )
				BEGIN
					SET @ASSEMBLY_YIELD = @ASSEMBLY_YIELD * 100
				END

				IF ( @ASSEMBLY_YIELD IS NULL OR @ASSEMBLY_YIELD <= 0.0 )
				BEGIN
					SET @ASSEMBLY_YIELD = 100.00
				END
			END
			ELSE IF (@PS_TYPE = 'Pre Test' )
			BEGIN
				SELECT @PRETEST_QTY = SUM(QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SET @PRETEST_QTY = ISNULL(@PRETEST_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
			END
			ELSE IF (@PS_TYPE = 'FT' )
			BEGIN
				--SELECT @TEST_YIELD = MIN(ISNULL( CASE WHEN @CURR_QTR = 1 THEN Q1_YIELD WHEN @CURR_QTR = 2 THEN Q2_YIELD WHEN @CURR_QTR = 3 THEN Q3_YIELD ELSE Q4_YIELD END, 100.00)) FROM UPDATE_VIEW_COST_TABLE  WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND YEAR(FIN_YEAR) = @CURR_YEAR
				SELECT @TEST_YIELD = ISNULL( CONVERT( FLOAT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Target Yield' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @TEST_QTY = SUM(QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TEST_CYCLE_TIME = ISNULL( CONVERT( INT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SET @TEST_QTY = ISNULL(@TEST_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
				IF ( @TEST_YIELD IS NULL OR @TEST_YIELD <= 0.0 )
				BEGIN
					SET @TEST_YIELD = 100.00
				END
			END
			ELSE IF (@PS_TYPE = 'Tape & Reel' )
			BEGIN
				SELECT @EOL_QTY = SUM(QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @TRANSIT_QTY = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE SL.Lot_Id = LT.Lot_ID AND SL.STATUS = 'TRANSIT' AND LT.PROCESSSTEP_ID = @PROCESSSTEP_ID AND ISNULL(LT.LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @EOL_CYCLE_TIME = ISNULL( CONVERT( INT, Parameter_Value), 0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SET @EOL_QTY = ISNULL(@EOL_QTY, 0) + ISNULL(@TRANSIT_QTY , 0)
			END
			ELSE IF (@PS_TYPE = 'FG' )
			BEGIN
				SELECT @FG_QTY = SUM(QTY_IN) FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_CURRENT_STATUS <> 'CLOSE' AND ISNULL(LOT_CATEGORY, 'PROD') <> 'ENGG'
				SELECT @CHIPSET = Parameter_Value FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Chipset1' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @FG_MIN_LEVEL_QTY = Parameter_Value FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Reorder Level' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @CHIP_FAMILY = Parameter_Value FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Chip Family' AND PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @PRODUCT_LINE = PRODUCT_GROUP FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
				SELECT @CQ_BACKLOG_QTY = SUM(COOL_QTY) - ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @PROCESSSTEP_ID AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @CURR_QTR_END_DATE AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) >= @CURRENT_QTR_START_DATE ), 0 ) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P WHERE COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @PROCESSSTEP_ID AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) <= @CURR_QTR_END_DATE AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) >= @CURRENT_QTR_START_DATE
			END
			FETCH NEXT FROM @PROCESSSTEP_CURSOR INTO @PROCESSSTEP_ID, @PS_TYPE, @PS_TYPE_NAME
		END
		IF(@FAB_YIELD <= 0.0 )
			SET @FAB_YIELD = 100.00
		IF(@ASSEMBLY_YIELD <= 0.0 )
			SET @ASSEMBLY_YIELD = 100.00
		IF(@TEST_YIELD <= 0.0 )
			SET @TEST_YIELD = 100.00
		IF(@SORT_YIELD <= 0.0 )
			SET @SORT_YIELD = 100.00
		IF @GROSS_DIE_PER_WAFER IS NULL OR @GROSS_DIE_PER_WAFER = 0
			SET @GROSS_DIE_PER_WAFER = 1

		IF @FAB_PART_NUMBER IS NULL OR LEN( @FAB_PART_NUMBER ) = 0
		BEGIN	
			SELECT @FAB_PART_NUMBER = PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @FG_PROCESSSTEP_ID
		END
		SET @ASSEMBLY_PART_NUMBER = NULL
		SELECT TOP 1 @ASSEMBLY_PART_NUMBER = PRODUCT_NAME FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @FG_PROCESSSTEP_ID, 0 )  WHERE PS_TYPE = 'ASSEMBLY'

		-- Fetching the Backlog by Month
		-- Month1 AND DELINQUENT QTY
		SET @BKL_START_DATE = @CURRENT_QTR_START_DATE
		SET @BKL_END_DATE  = DATEADD( WW, 4, @CURRENT_QTR_START_DATE )
		
		SELECT @DELINQUENT_BACKLOG_QTY = SUM(COOL_Qty)-ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @BKL_START_DATE ), 0) FROM Customer_Open_Order_LineItem COOL, Product P WHERE COOL.Product_ID = P.Product_ID AND COOL_STATUS <> 'SHIPPED' AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) < @BKL_START_DATE
		
		SELECT @M1_BACKLOG_QTY = SUM(COOL_Qty)-ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @BKL_END_DATE AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) >= @BKL_START_DATE ), 0) FROM Customer_Open_Order_LineItem COOL, Product P WHERE COOL.Product_ID = P.Product_ID AND COOL_STATUS <> 'SHIPPED' AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) < @BKL_END_DATE AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) >= @BKL_START_DATE
		-- Month2
		SET @BKL_START_DATE = @BKL_END_DATE
		SET @BKL_END_DATE  = DATEADD( WW, 4, @BKL_START_DATE )
		SELECT @M2_BACKLOG_QTY = SUM(COOL_Qty)-ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @BKL_END_DATE AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) >= @BKL_START_DATE ), 0) FROM Customer_Open_Order_LineItem COOL, Product P WHERE COOL.Product_ID = P.Product_ID AND COOL_STATUS <> 'SHIPPED' AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) < @BKL_END_DATE  AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) >= @BKL_START_DATE

		-- Month3
		SET @BKL_START_DATE = @BKL_END_DATE
		SET @BKL_END_DATE  = DATEADD( WW, 5, @BKL_START_DATE )
		SELECT @M3_BACKLOG_QTY = SUM(COOL_Qty)-ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @BKL_END_DATE AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) >= @BKL_START_DATE ), 0) FROM Customer_Open_Order_LineItem COOL, Product P WHERE COOL.Product_ID = P.Product_ID AND COOL_STATUS <> 'SHIPPED' AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) < @BKL_END_DATE  AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) >= @BKL_START_DATE

		-- Month4
		SET @BKL_START_DATE = @BKL_END_DATE
		SET @BKL_END_DATE  = DATEADD( WW, 4, @BKL_START_DATE )
		SELECT @M4_BACKLOG_QTY = SUM(COOL_Qty)-ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @BKL_END_DATE AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) >= @BKL_START_DATE ), 0) FROM Customer_Open_Order_LineItem COOL, Product P WHERE COOL.Product_ID = P.Product_ID AND COOL_STATUS <> 'SHIPPED' AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) < @BKL_END_DATE  AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) >= @BKL_START_DATE

		-- Month5
		SET @BKL_START_DATE = @BKL_END_DATE
		SET @BKL_END_DATE  = DATEADD( WW, 4, @BKL_START_DATE )
		SELECT @M5_BACKLOG_QTY = SUM(COOL_Qty)-ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @BKL_END_DATE AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) >= @BKL_START_DATE ), 0) FROM Customer_Open_Order_LineItem COOL, Product P WHERE COOL.Product_ID = P.Product_ID AND COOL_STATUS <> 'SHIPPED' AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) < @BKL_END_DATE  AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) >= @BKL_START_DATE

		-- Month6
		SET @BKL_START_DATE = @BKL_END_DATE
		SET @BKL_END_DATE  = DATEADD( WW, 5, @BKL_START_DATE )
		SELECT @M6_BACKLOG_QTY = SUM(COOL_Qty)-ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @BKL_END_DATE AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) >= @BKL_START_DATE ), 0) FROM Customer_Open_Order_LineItem COOL, Product P WHERE COOL.Product_ID = P.Product_ID AND COOL_STATUS <> 'SHIPPED' AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) < @BKL_END_DATE  AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) >= @BKL_START_DATE

		-- Month7
		SET @BKL_START_DATE = @BKL_END_DATE
		SET @BKL_END_DATE  = DATEADD( WW, 4, @BKL_START_DATE )
		SELECT @M7_BACKLOG_QTY = SUM(COOL_Qty)-ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @BKL_END_DATE AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) >= @BKL_START_DATE ), 0) FROM Customer_Open_Order_LineItem COOL, Product P WHERE COOL.Product_ID = P.Product_ID AND COOL_STATUS <> 'SHIPPED' AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) < @BKL_END_DATE  AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) >= @BKL_START_DATE

		-- Month8
		SET @BKL_START_DATE = @BKL_END_DATE
		SET @BKL_END_DATE  = DATEADD( WW, 4, @BKL_START_DATE )
		SELECT @M8_BACKLOG_QTY = SUM(COOL_Qty)-ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @BKL_END_DATE AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) >= @BKL_START_DATE ), 0) FROM Customer_Open_Order_LineItem COOL, Product P WHERE COOL.Product_ID = P.Product_ID AND COOL_STATUS <> 'SHIPPED' AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) < @BKL_END_DATE  AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) >= @BKL_START_DATE

		-- Month9
		SET @BKL_START_DATE = @BKL_END_DATE
		SET @BKL_END_DATE  = DATEADD( WW, 5, @BKL_START_DATE )
		SELECT @M9_BACKLOG_QTY = SUM(COOL_Qty)-ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @BKL_END_DATE AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) >= @BKL_START_DATE ), 0) FROM Customer_Open_Order_LineItem COOL, Product P WHERE COOL.Product_ID = P.Product_ID AND COOL_STATUS <> 'SHIPPED' AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) < @BKL_END_DATE  AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) >= @BKL_START_DATE

		-- Month10
		SET @BKL_START_DATE = @BKL_END_DATE
		SET @BKL_END_DATE  = DATEADD( WW, 4, @BKL_START_DATE )
		SELECT @M10_BACKLOG_QTY = SUM(COOL_Qty)-ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @BKL_END_DATE AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) >= @BKL_START_DATE ), 0) FROM Customer_Open_Order_LineItem COOL, Product P WHERE COOL.Product_ID = P.Product_ID AND COOL_STATUS <> 'SHIPPED' AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) < @BKL_END_DATE  AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) >= @BKL_START_DATE

		-- Month11
		SET @BKL_START_DATE = @BKL_END_DATE
		SET @BKL_END_DATE  = DATEADD( WW, 4, @BKL_START_DATE )
		SELECT @M11_BACKLOG_QTY = SUM(COOL_Qty)-ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @BKL_END_DATE AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) >= @BKL_START_DATE ), 0) FROM Customer_Open_Order_LineItem COOL, Product P WHERE COOL.Product_ID = P.Product_ID AND COOL_STATUS <> 'SHIPPED' AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) < @BKL_END_DATE  AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) >= @BKL_START_DATE

		-- Month12
		SET @BKL_START_DATE = @BKL_END_DATE
		SET @BKL_END_DATE  = DATEADD( WW, 5, @BKL_START_DATE )
		SELECT @M12_BACKLOG_QTY = SUM(COOL_Qty)-ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) < @BKL_END_DATE AND ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) >= @BKL_START_DATE ), 0) FROM Customer_Open_Order_LineItem COOL, Product P WHERE COOL.Product_ID = P.Product_ID AND COOL_STATUS <> 'SHIPPED' AND P.ProcessStep_ID = @FG_PROCESSSTEP_ID AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) < @BKL_END_DATE  AND ISNULL( COOL.COOL_REVISED_DATE, ISNULL( COOL.COOL_SCHEDULE_DATE, COOL.COOL_REQUESTED_DATE ) ) >= @BKL_START_DATE

		UPDATE #RESULT_TABLE SET FAB_OPEN_PO_QTY = @OPEN_PO_QTY, FAB_QTY = @FAB_QTY, WAFER_STORE_QTY = @WAFERSTORE_QTY, SORT_QTY = @SORT_QTY, DIE_BANK_QTY = @DIEBANK_QTY, ASSEMBLY_QTY = @ASSEMBLY_QTY, PRE_TEST = @PRETEST_QTY, 
		TEST_QTY = @TEST_QTY, EOL_QTY = @EOL_QTY, FG_QTY = @FG_QTY, GROSS_DIE_PER_WAFER = @GROSS_DIE_PER_WAFER, FAB_PART_NUMBER = @FAB_PART_NUMBER,
		FAB_YIELD = @FAB_YIELD/100, SORT_YIELD = @SORT_YIELD/100, ASSEMBLY_YIELD = @ASSEMBLY_YIELD/100, TEST_YIELD = @TEST_YIELD/100, CHIP_SET = @CHIPSET,
		Fab_CYCLE_TIME = @FAB_CYCLE_TIME, Sort_CYCLE_TIME = @SORT_CYCLE_TIME, Assembly_CYCLE_TIME = @ASSEMBLY_CYCLE_TIME, TEST_CYCLE_TIME = @TEST_CYCLE_TIME, EOL_CYCLE_TIME = @EOL_CYCLE_TIME,
		Chip_Family = @CHIP_FAMILY, Product_Line = @PRODUCT_LINE, Process = @PROCESS, Wafer_Cost = @Wafer_Cost, CQ_BACKLOG_QTY = @CQ_BACKLOG_QTY, DIES_PER_PACKAGE = @DIES_PER_PACKAGE,
		ASSEMBLY_PART_NUMBER = ISNULL(@ASSEMBLY_PART_NUMBER,FG_PART_NUMBER), ASSEMBLY_RELEASE_QTY = @ASSEMBLY_RELEASE_QTY, DELINQUENT_BACKLOG_QTY = @DELINQUENT_BACKLOG_QTY,
		M1_BACKLOG_QTY = @M1_BACKLOG_QTY, M2_BACKLOG_QTY = @M2_BACKLOG_QTY, M3_BACKLOG_QTY = @M3_BACKLOG_QTY, M4_BACKLOG_QTY = @M4_BACKLOG_QTY,
		M5_BACKLOG_QTY = @M5_BACKLOG_QTY, M6_BACKLOG_QTY = @M6_BACKLOG_QTY, M7_BACKLOG_QTY = @M7_BACKLOG_QTY, M8_BACKLOG_QTY = @M8_BACKLOG_QTY,
		M9_BACKLOG_QTY = @M9_BACKLOG_QTY, M10_BACKLOG_QTY = @M10_BACKLOG_QTY, M11_BACKLOG_QTY = @M11_BACKLOG_QTY, M12_BACKLOG_QTY = @M12_BACKLOG_QTY
		WHERE FG_PS_ID = @FG_PROCESSSTEP_ID

		CLOSE @PROCESSSTEP_CURSOR
		DEALLOCATE @PROCESSSTEP_CURSOR
		FETCH NEXT FROM @FORECAST_CURSOR INTO @FG_PROCESSSTEP_ID
	END
	CLOSE @FORECAST_CURSOR
	DEALLOCATE @FORECAST_CURSOR
	
	SELECT * FROM #RESULT_TABLE ORDER BY FAB_PART_NUMBER, FG_PART_NUMBER
END